package org.sklse.learningassistant.dao;

import java.util.List;

import javax.annotation.Resource;

import org.sklse.learningassistant.bean.Plan;
import org.sklse.learningassistant.bean.Sign;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class PlanDao {

	@Resource
	private JdbcTemplate jdbcTemplate;
	
	public List<Plan> getPlan (){
		
		String sql = "select *from plan";
		RowMapper<Plan> rowMapper = new BeanPropertyRowMapper<>(Plan.class);
		List<Plan> planList = jdbcTemplate.query(sql, new Object[]{}, rowMapper);
		
		return planList;
	}
	
	public List<Sign> getSign(Integer pID) {
		String sql = "select * from sign where pID=?";
		RowMapper<Sign> rowMapper = new BeanPropertyRowMapper<>(Sign.class);
		List<Sign> signList = jdbcTemplate.query(sql, new Object[] { pID }, rowMapper);

		return signList;
	}
	/*public void addPlan(Integer pID, String p_name, String description, Integer signed_num, Integer total_num,
			String bell, String wName, Integer hour, Integer minute) {
		String sql1 = "insert into plan values(" + pID + "," + bell + "," + description + "," + hour + ","
				+ minute + "," + p_name + "," + signed_num + "," + total_num + "," + wName + ")";
		jdbcTemplate.execute(sql1);

		for (int i = 1; i <= total_num; i++) {
			// 自增长的主键可以不填
			String sql2 = "insert into sign(pid, day, flag) values(" + pID + "," + i + "," + 0 + ")";
			jdbcTemplate.execute(sql2);
		}
		String sql3 = "insert into wish values(" + pID + "," + wName + ")";
		jdbcTemplate.execute(sql3);
	}*/
	public void addSign(Integer pID, Integer total_num) {

		for (int i = 1; i <= total_num; i++) {
			// 自增长的主键可以不填
			String sql = "insert into sign(pid, day, flag) values(" + pID + "," + i + "," + 0 + ")";
			jdbcTemplate.execute(sql);
		}
	}

	
	public void updatePlan(String p_name, String description, Integer signed_num, Integer total_num, Integer pID) {
		String sql = "update plan set p_name=?, description=?, signed_num=?, total_num=? where pID=?";
		jdbcTemplate.update(sql, new Object[] { p_name, description, signed_num, total_num, pID });
	}

	public void deletePlan(Integer pID) {
		String sql1 = "delete from sign where pID=" + pID;
		jdbcTemplate.execute(sql1);
		String sql2 = "delete from wish where pID=" + pID;
		jdbcTemplate.execute(sql2);
		String sql3 = "delete from plan where pID=" + pID;
		jdbcTemplate.execute(sql3);
	}

	public void sign(Integer pID, Integer day, Integer signedNum) {
		String sql1 = "update sign set flag = 1 where pID=" + pID + " and day=" + day;
		jdbcTemplate.execute(sql1);
		signedNum++;
		String sql2 = "update plan set signed_num = " + signedNum + " where pID=" + pID;
		jdbcTemplate.execute(sql2);
	}
}
